﻿

using Dapper;
using HBase;
using hCommand;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;

namespace Persistence.abs
{
    public abstract class AbsCoreWhLocServer : AbsWMSBaseServer, ICoreWhLocServer
    {

        public Page<CoreWhLoc> queryPhysicalLocationPage<CoreWhLoc>(int start, int end, PageDto param)
        {

            string sql = "physical_Location";
            string orderBy = "id";
            return this.queryPage<CoreWhLoc>(sql, orderBy, param);

        }
        public CoreWhLoc getById(long id )
        {
            return getById<CoreWhLoc>(id );
        }
        public List<DapingDto> getDapingDto(int id) {
            String sql = "SELECT [loc_Id] l, [status] s  FROM  [Core_stock]  where status >0 ";
            if (id == 1) {
                sql += " and [loc_Id]<500000";
            } else if (id == 2) {
                sql += " and [loc_Id]>500000  and [loc_Id]<900000";
            }

            return readConn().Query<DapingDto>(sql).ToList();
        }

        #region 区域设置
        //区域添加
        public long AddArea(CoreWhArea model)
        {

            model.AreaName = model.AreaName;

            model.info = model.info;

            model.orderNo = model.orderNo;
            model.type = model.type;
            // model.name =model.name;
            return opConn().Insert<CoreWhArea>(model);
        }
        //区域修改
        public bool UpdateArea(CoreWhArea coreWhArea)
        {
            return opConn().Update<CoreWhArea>(coreWhArea);
        }
        //区域删除
        public int DeleteArea(List<CoreWhArea> areaList)
        {
            foreach (var coreWhArea in areaList)
            {
                opConn().Delete<CoreWhArea>("id=" + coreWhArea.id.ToString());
            }
            return 1;
        }
        //通过区域ID查找
        public CoreWhArea FindAreaById(long id)
        {
            return readConn().Get<CoreWhArea>(id);
        }
        //区域名称查重
        public bool QueryAreaPageByName(CoreWhArea coreWhArea)
        {
            string sql = @"select * from Core_wh_Area where Area_Name='{0}'";
            sql = string.Format(sql, coreWhArea.AreaName);
            List<CoreWhArea> list = readConn().Query<CoreWhArea>(sql, coreWhArea).ToList();
            if (list.Count == 0)
                return false;
            else
                return true;
        }
        //区域分页显示
        public Page<CoreWhArea> QueryAreaPage(CoreWhAreaDto dto)
        {
            string sql = @"select ca.* from Core_wh_Area ca where 1=1";
            if (dto != null && !string.IsNullOrEmpty(dto.AreaName))
            {
                dto.AreaName = "%" + dto.AreaName + "%";
                sql += " AND ca.Area_Name like @AreaName";
            }
            if (dto != null && dto.whId != 0)
            {
                sql += " AND ca.wh_Id = @whId";
            }
            if (dto != null && !string.IsNullOrEmpty(dto.orderNo))
            {
                sql += " AND ca.order_No = @orderNo";
            }
            // Page<ClassQuery> info= queryPage<ClassQuery>(sql, "id", dto);
            return this.queryPage<CoreWhArea>(sql, "order_No desc ", dto);
        }
        //根据库房ID查询数据
        public List<CoreWhArea> GetAreaByWhId(long whId)
        {
            String sql = "select * from Core_wh_Area ca where ca.wh_Id=@whId";
            return readConn().Query<CoreWhArea>(sql, new { whId = whId }).ToList();
        }

        public List<CoreWhAreaStat> stat() {
            String sql = "SELECT a.id, Area_Name ,count(*) 'all',sum(Count_stock) 'use',max(a.order_No) order_No FROM `core_wh_area` a join core_wh_loc l on a.id=l.area_Id GROUP BY Area_Name ,a.id order by order_No  desc  ";
            return readConn().Query<CoreWhAreaStat>(sql).ToList();
        }
        #endregion

        #region 库位设置
        //库位分页
        public Page<CoreWhLoc> QueryLocPage(CoreWhLocDto dto)
        {
            string sql = @"select cl.id,cl.crane_Id,cl.Shelf_Id,
                           cl.area_Id,cl.Row_No,cl.Col_No,
                           cl.type,cl.order_No,cl.deep_Index,
                           cl.max_stock,
                           cl.Count_stock,cl.move_group,
                           cl.Active_Status
                           
                           from Core_wh_Loc cl where max_stock>0";
            if (dto != null)
            {
                if (dto != null && dto.areaId != 0)
                {
                    sql += " AND cl.area_Id=@areaId";
                }
                if (dto.EndRowNumber != 0 && dto.RowNumber != 0)
                {
                    sql += " AND cl.Row_No>=@RowNumber and cl.Row_No<=@EndRowNumber";
                }
                else if (dto.RowNumber != 0)
                {
                    sql += " AND cl.Row_No=@RowNumber";
                }

                if (dto.EndColNumber != 0 && dto.ColNumber != 0)
                {
                    sql += " AND cl.Col_No>=@ColNumber and cl.Col_No<=@EndColNumber";
                }
                else if (dto != null && dto.ColNumber != 0)
                {
                    sql += " AND cl.Col_No=@ColNumber";
                }

                if (dto != null && dto.ShelfId != 0)
                {
                    sql += " AND cl.shelf_Id = @ShelfId";
                }
                if (!string.IsNullOrEmpty(dto.ActiveStatus))
                {
                    sql += " AND cl.Active_Status = @ActiveStatus";
                }
            }

            return this.queryPage<CoreWhLoc>(sql, "id", dto);
        }
        //库位添加
        public long AddLoc(CoreWhLoc model)
        {
            return opConn().Insert<CoreWhLoc>(model);
        }
        //库位修改
        public bool UpdateLoc(CoreWhLoc coreWhLoc)
        {

            String sql = "update Core_Wh_Loc set area_Id= @areaId,  Active_Status = @activeStatus where id=@id ";
            return opConn().Execute(sql, coreWhLoc) == 1;
        }

        public void batchUpdateLocScr(List<long> locList, long areaId, int activeStatus) {
            List<int> scrList = new List<int>();
            foreach (var locId in locList) {
                int scr = (int)locId / 10;
                if (scrList.Contains(scr)) {
                    continue;
                }
                scrList.Add(scr);
            }
            string sql = "update Core_Wh_Loc set area_Id= @areaId,  Active_Status = @activeStatus where  where scr in @scr ";
            if (activeStatus == -1) {
                sql = "update Core_Wh_Loc set area_Id= @areaId   where scr in @scr ";
            }
            opConn().Execute(sql, new { scr = scrList, areaId, activeStatus });
        }
        //库位删除
        public int DeleteLoc(List<CoreWhLoc> locList)
        {
            foreach (var coreWhLoc in locList)
            {
                opConn().Delete<CoreWhLoc>("id=" + coreWhLoc.id.ToString());
            }
            return 1;
        }
        //通过库位ID查找信息
        public CoreWhLoc FindLocById(long id)
        {
            return readConn().Get<CoreWhLoc>(id);
        }
        //通过库位名称查找信息
        public bool QueryLocPageByRow(CoreWhLoc coreWhLoc)
        {
            string sql = @"select cl.* from Core_wh_Loc cl where cl.Row_No='{0}' and cl.Col_No='{1}'";
            sql = string.Format(sql, coreWhLoc.RowNo, coreWhLoc.ColNo);
            List<CoreWhArea> list = readConn().Query<CoreWhArea>(sql, coreWhLoc).ToList();
            if (list.Count == 0)
                return false;
            else
                return true;
        }
        //通过区域ID查找信息
        public List<CoreWhLoc> GetLocByWhAreaId(long areaId)
        {
            String sql = "select cl.* from Core_wh_Loc cl where cl.area_Id=@areaId";
            return readConn().Query<CoreWhLoc>(sql, new { areaId = areaId }).ToList();
        }
        //批量修改库位的库区信息
        public bool BatchUpdataLoc(List<long> locList, long areaid)
        {
            for (int i = 0; i < locList.Count(); i++)
            {
                long iid = locList[i];
                string sql = @"update core_Wh_loc set area_id=@areaId where id=@ids";
                opConn().Execute(sql, new { areaId = areaid, ids = locList[i] });
            }
            return true;
        }

        public bool updateWcsAutoActive(int craneId, int status)
        {
            String sql = "update core_wh_group set wcs_Auto_active=@status where crane_Id=@craneId";
            opConn().Execute(sql, new { craneId, status });
            return true;

        }
        #endregion
    }
}
